package pl.edu.pw.perg.compinfodb.xls;

import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.LIST_TYPE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.LOCATION_NOT_AVAILABLE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.NUMERIC_TYPE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.TEXT_TYPE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.TRANSPARENT_TRUE;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeSet;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import pl.edu.pw.perg.compinfodb.entities.ComplexElement;
import pl.edu.pw.perg.compinfodb.entities.Connector;
import pl.edu.pw.perg.compinfodb.entities.ElementType;
import pl.edu.pw.perg.compinfodb.entities.InternalConnection;
import pl.edu.pw.perg.compinfodb.entities.PhysicalElement;
import pl.edu.pw.perg.compinfodb.entities.VirtualElement;
import pl.edu.pw.perg.compinfodb.entities.field.Field;
import pl.edu.pw.perg.compinfodb.entities.field.ListField;
import pl.edu.pw.perg.compinfodb.entities.field.ListFieldItem;
import pl.edu.pw.perg.compinfodb.entities.field.NumericField;
import pl.edu.pw.perg.compinfodb.entities.field.TextField;

public class XLSMetadataReader {

	private static int DEFAULT_FIRST_INDEX;
	private static int IC_OUT_COL;
	private static int IC_IN_COL;
	private static int IC_TYPE_COL;
	private static int E_IDENTIFIER_COL;
	private static int E_LABEL_COL;
	private static int E_TYPE_COL;
	private static int C_IN_CON_COL;
	private static int C_OUT_CON_COL;
	private static int C_IN_EL_COL;
	private static int C_OUT_EL_COL;
	private static int C_CABLE_COL;
	private static int S_LOCATION_COL;
	private static int S_PARENT_COL;
	private static int S_TYPE_COL;
	private static int S_NAME_COL;
	private static int T_FIELDS_COL;
	private static int T_IN_CON_COL;
	private static int T_OUT_CON_COL;
	private static int T_TRANSPARENT_COL;
	private static int T_PARENT_COL;
	private static int T_NAME_COL;
	private static int T_INDEXING_START_FROM_COL;
	private static int T_CABLE_FLAG_COL;
	private static int F_MIN_LIST_ITEMS;
	private static int F_LIST_ITEMS_COL;
	private static int F_TYPE_COL;
	private static int F_NAME_COL;
	private static int HEADER_ROWS;

	private static final String CONNECTIONS_SHEET = "connections_sheet";
	private static final String CONNECTIONS_SHEET_LOCATION = "connections_sheet_location";
	private static final String INTERNAL_CONNECTIONS_SHEET = "internal_connections_sheet";
	private static final String INTERNAL_CONNECTIONS_SHEET_LOCATION = "internal_connections_sheet_location";
	private static final String ELEMENT_CABLES_SHEET = "element_cables_sheet";
	private static final String ELEMENT_CABLES_SHEET_LOCATION = "element_cables_sheet_location";
	private static final String ELEMENT_MODULES_SHEET = "element_modules_sheet";
	private static final String ELEMENT_MODULES_SHEET_LOCATION = "element_modules_sheet_location";
	private static final String STRUCTURE_CABLES_SHEET = "structure_cables_sheet";
	private static final String STRUCTURE_CABLES_SHEET_LOCATION = "structure_cables_sheet_location";
	private static final String STRUCTURE_MODULES_SHEET = "structure_modules_sheet";
	private static final String CONNECTOR_NAMES_SHEET_LOCATION = "connector_names_sheet_location";
	private static final String STRUCTURE_MODULES_SHEET_LOCATION = "structure_modules_sheet_location";
	private static final String CONNECTOR_NAMES_SHEET = "connector_names_sheet";
	private static final String HISTORY_SHEET = "history_sheet";
	private static final String HISTORY_SHEET_LOCATION = "history_sheet_location";
	private static final String TYPES_SHEET = "types_sheet";
	private static final String TYPES_SHEET_LOCATION = "types_sheet_location";
	private static final String FIELDS_SHEET = "fields_sheet";
	private static final String FIELDS_SHEET_LOCATION = "fields_sheet_location";
	private static final String CONNECTIONS_FILE = "connections_file";
	private static final String ELEMENT_CABLES_FILE = "element_cables_file";
	private static final String ELEMENT_MODULES_FILE = "element_modules_file";
	private static final String STRUCTURE_CABLES_FILE = "structure_cables_file";
	private static final String STRUCTURE_MODULES_FILE = "structure_modules_file";
	private static final String FIELDS_TYPES_HISTORY_FILE = "fields_types_history_file";
	private static final String PROPERTIES_FILE = "xls-validator.properties";
	private static final String TEST_PROPERTIES_FILE = "xls-validator-test.properties";

	private Sheet fieldsSheet;
	private Sheet typesSheet;
	private Sheet historySheet;
	private Sheet modulesSheet;
	private Sheet cablesSheet;
	private Sheet connectionsSheet;
	private Sheet elementModulesSheet;
	private Sheet elementCablesSheet;
	private Sheet internalConnectionSheet;
	private Sheet connectorNamesSheet;

	private Workbook wbookFTH;
	private Workbook wbookSM;
	private Workbook wbookSC;
	private Workbook wbookEM;
	private Workbook wbookEC;
	private Workbook wbookC;

	private static boolean test;

	public static Properties props;

	static void init() {
		props = new Properties();
		String file = test ? TEST_PROPERTIES_FILE : PROPERTIES_FILE;
		try {

			props.load(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			System.err.println("File: " + file + " not found!");
			System.exit(1);
		} catch (IOException e) {
			System.err.println("I/O Exception occured reading file: " + file);
			System.exit(1);
		}

	}

	Map<String, Workbook> workbooks = new HashMap<String, Workbook>();

	private Map<String, Field> fieldMap = new HashMap<String, Field>();
	private Map<String, ElementType> typeMap = new HashMap<String, ElementType>();
	private Map<String, VirtualElement> elementMap = new HashMap<String, VirtualElement>();
	// private Map<String, VirtualElement> cableMap = new HashMap<String,
	// VirtualElement>();
	private Map<ElementType, Integer> startIndexingMap = new HashMap<ElementType, Integer>();
	private Map<VirtualElement, PhysicalElement> complexMap = new HashMap<VirtualElement, PhysicalElement>();
	
	public XLSMetadataReader() {
		init();
		boolean correctInitialisation = true;
		try {
			wbookFTH = Workbook.getWorkbook(new File(props
					.getProperty(FIELDS_TYPES_HISTORY_FILE)));
			wbookSM = Workbook.getWorkbook(new File(props
					.getProperty(STRUCTURE_MODULES_FILE)));
			wbookSC = Workbook.getWorkbook(new File(props
					.getProperty(STRUCTURE_CABLES_FILE)));
			wbookEM = Workbook.getWorkbook(new File(props
					.getProperty(ELEMENT_MODULES_FILE)));
			wbookEC = Workbook.getWorkbook(new File(props
					.getProperty(ELEMENT_CABLES_FILE)));
			wbookC = Workbook.getWorkbook(new File(props
					.getProperty(CONNECTIONS_FILE)));
		} catch (BiffException e) {
			System.err.println("Cannot load spreadsheets!");
			System.err.println("Cause: " + e.getMessage());
			System.exit(1);
			e.printStackTrace();
		} catch (IOException e) {
			System.err.println("Cannot load spreadsheets!");
			System.err.println(e.getMessage());
			System.exit(1);
		}

		workbooks.put(FIELDS_TYPES_HISTORY_FILE, wbookFTH);
		workbooks.put(STRUCTURE_MODULES_FILE, wbookSM);
		workbooks.put(STRUCTURE_CABLES_FILE, wbookSC);
		workbooks.put(ELEMENT_MODULES_FILE, wbookEM);
		workbooks.put(ELEMENT_CABLES_FILE, wbookEC);
		workbooks.put(CONNECTIONS_FILE, wbookC);

		fieldsSheet = workbooks.get(props.getProperty(FIELDS_SHEET_LOCATION))
				.getSheet(props.getProperty(FIELDS_SHEET));
		if (fieldsSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(FIELDS_SHEET))));
			correctInitialisation = false;
		}
		typesSheet = workbooks.get(props.getProperty(TYPES_SHEET_LOCATION))
				.getSheet(props.getProperty(TYPES_SHEET));
		if (typesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(TYPES_SHEET))));
			correctInitialisation = false;
		}
		historySheet = workbooks.get(props.getProperty(HISTORY_SHEET_LOCATION))
				.getSheet(props.getProperty(HISTORY_SHEET));
		if (historySheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(HISTORY_SHEET))));
			correctInitialisation = false;
		}
		connectorNamesSheet = workbooks.get(
				props.getProperty(CONNECTOR_NAMES_SHEET_LOCATION)).getSheet(
				props.getProperty(CONNECTOR_NAMES_SHEET));
		if (connectorNamesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(CONNECTOR_NAMES_SHEET))));
			correctInitialisation = false;
		}
		modulesSheet = workbooks.get(
				props.getProperty(STRUCTURE_MODULES_SHEET_LOCATION)).getSheet(
				props.getProperty(STRUCTURE_MODULES_SHEET));
		if (modulesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(STRUCTURE_MODULES_SHEET))));
			correctInitialisation = false;
		}
		cablesSheet = workbooks.get(
				props.getProperty(STRUCTURE_CABLES_SHEET_LOCATION)).getSheet(
				props.getProperty(STRUCTURE_CABLES_SHEET));
		if (cablesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(STRUCTURE_CABLES_SHEET))));
			correctInitialisation = false;
		}
		elementModulesSheet = workbooks.get(
				props.getProperty(ELEMENT_MODULES_SHEET_LOCATION)).getSheet(
				props.getProperty(ELEMENT_MODULES_SHEET));
		if (elementModulesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(ELEMENT_MODULES_SHEET))));
			correctInitialisation = false;
		}
		elementCablesSheet = workbooks.get(
				props.getProperty(ELEMENT_CABLES_SHEET_LOCATION)).getSheet(
				props.getProperty(ELEMENT_CABLES_SHEET));
		if (elementCablesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(ELEMENT_CABLES_SHEET))));
			correctInitialisation = false;
		}
		internalConnectionSheet = workbooks.get(
				props.getProperty(INTERNAL_CONNECTIONS_SHEET_LOCATION))
				.getSheet(props.getProperty(INTERNAL_CONNECTIONS_SHEET));
		if (internalConnectionSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(INTERNAL_CONNECTIONS_SHEET))));
			correctInitialisation = false;
		}
		connectionsSheet = workbooks.get(
				props.getProperty(CONNECTIONS_SHEET_LOCATION)).getSheet(
				props.getProperty(CONNECTIONS_SHEET));
		if (connectionsSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found!", XLSSheets.findSheet(props
							.getProperty(CONNECTIONS_SHEET))));
			correctInitialisation = false;
		}

		try {

			DEFAULT_FIRST_INDEX = Integer.parseInt(props
					.getProperty("default_first_index"));
			IC_OUT_COL = convertCharToInt(props.getProperty("ic_out_col")
					.charAt(0));
			IC_IN_COL = convertCharToInt(props.getProperty("ic_in_col").charAt(
					0));
			IC_TYPE_COL = convertCharToInt(props.getProperty("ic_type_col")
					.charAt(0));
			E_IDENTIFIER_COL = convertCharToInt(props.getProperty(
					"e_identifier_col").charAt(0));
			E_LABEL_COL = convertCharToInt(props.getProperty("e_label_col")
					.charAt(0));
			E_TYPE_COL = convertCharToInt(props.getProperty("e_type_col")
					.charAt(0));
			C_IN_CON_COL = convertCharToInt(props.getProperty("c_in_con_col")
					.charAt(0));
			C_OUT_CON_COL = convertCharToInt(props.getProperty("c_out_con_col")
					.charAt(0));
			C_IN_EL_COL = convertCharToInt(props.getProperty("c_in_el_col")
					.charAt(0));
			C_OUT_EL_COL = convertCharToInt(props.getProperty("c_out_el_col")
					.charAt(0));
			C_CABLE_COL = convertCharToInt(props.getProperty("c_cable_col")
					.charAt(0));
			S_LOCATION_COL = convertCharToInt(props.getProperty(
					"s_position_col").charAt(0));
			S_PARENT_COL = convertCharToInt(props.getProperty("s_parent_col")
					.charAt(0));
			S_TYPE_COL = convertCharToInt(props.getProperty("s_type_col")
					.charAt(0));
			S_NAME_COL = convertCharToInt(props.getProperty("s_identifier_col")
					.charAt(0));
			T_CABLE_FLAG_COL = convertCharToInt(props.getProperty(
			"t_cable_flag_col").charAt(0));
			T_INDEXING_START_FROM_COL = convertCharToInt(props.getProperty(
					"t_indexing_start_from_col").charAt(0));
			T_FIELDS_COL = convertCharToInt(props.getProperty("t_fields_col")
					.charAt(0));
			T_IN_CON_COL = convertCharToInt(props.getProperty(
					"t_input_cons_col").charAt(0));
			T_OUT_CON_COL = convertCharToInt(props.getProperty(
					"t_output_con_col").charAt(0));
			T_TRANSPARENT_COL = convertCharToInt(props.getProperty(
					"t_transparent_col").charAt(0));
			T_PARENT_COL = convertCharToInt(props.getProperty("t_parent_col")
					.charAt(0));
			T_NAME_COL = convertCharToInt(props.getProperty(
					"t_element_type_col").charAt(0));
			F_MIN_LIST_ITEMS = Integer.parseInt(props
					.getProperty("f_minimum_list_items"));
			F_LIST_ITEMS_COL = convertCharToInt(props.getProperty(
					"f_list_items_col").charAt(0));
			F_TYPE_COL = convertCharToInt(props.getProperty("f_field_type_col")
					.charAt(0));
			F_NAME_COL = convertCharToInt(props.getProperty("f_field_name_col")
					.charAt(0));
			HEADER_ROWS = Integer.parseInt(props.getProperty("header_rows"));

		} catch (NumberFormatException e) {
			System.err
					.println("One of numeric properties is not a number. Check properties file!");
			System.err.println(e.getMessage());
			System.exit(1);
		}
		if (!correctInitialisation)
			System.exit(1);

	}

	public List<Field> getFields() throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(fieldsSheet.getName());
		if (fieldsSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found! Check sheets names. ", sheet));
			return null;
		}
		List<Field> fields = new ArrayList<Field>();
		for (int i = HEADER_ROWS; i < fieldsSheet.getRows(); i++) {
			String name = fieldsSheet.getCell(F_NAME_COL, i).getContents();
			String type = fieldsSheet.getCell(F_TYPE_COL, i).getContents();
			if (name.equals("") && type.equals(""))
				continue;
			if (name.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Field name cannot be empty!", sheet, String.valueOf(
								convertIntToChar(F_NAME_COL)).toUpperCase(), i));

			if (type.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Field type cannot be empty!", sheet, String.valueOf(
								convertIntToChar(F_TYPE_COL)).toUpperCase(), i));
			Field field;
			if (type.equals(TEXT_TYPE))
				field = new TextField();
			else if (type.equals(NUMERIC_TYPE))
				field = new NumericField();
			else if (type.equals(LIST_TYPE)) {
				field = new ListField();
				Set<ListFieldItem> items = new TreeSet<ListFieldItem>();
				int position = 0;
				for (int j = F_LIST_ITEMS_COL; j < fieldsSheet.getColumns(); j++) {
					String listItem = fieldsSheet.getCell(j, i).getContents();
					if (listItem.equals("")) {
						if (j < F_LIST_ITEMS_COL + F_MIN_LIST_ITEMS)
							handleFormatError(new InvalidMetadataFormatException(
									"List field should define at least 2 list "
											+ "items!", sheet, String.valueOf(
											convertIntToChar(j)).toUpperCase(),
									i));
						break;
					}
					ListFieldItem lfi = new ListFieldItem(listItem);
					lfi.setPosition(position++);
					items.add(lfi);
				}
				((ListField) field).setListFieldItems(items);
			} else {
				handleFormatError(new InvalidMetadataFormatException(
						"Wrong field type - should " + "be one of:" + TEXT_TYPE
								+ ", " + NUMERIC_TYPE + ", " + LIST_TYPE
								+ " but was: " + type, sheet, String.valueOf(
								convertIntToChar(F_TYPE_COL)).toUpperCase(), i));
				continue;
			}
			field.setFieldName(name);
			if (fieldMap.containsKey(name)) {
				handleFormatError(new InvalidMetadataFormatException(
						"Duplicate definition of field: " + name, sheet, String
								.valueOf(convertIntToChar(F_NAME_COL))
								.toUpperCase(), i));
				continue;
			}
			fieldMap.put(name, field);
			fields.add(field);
		}
		return fields;
	}

	public List<ElementType> getTypes() throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(typesSheet.getName());
		Map<ElementType, String> parents = new HashMap<ElementType, String>();
		if (typesSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found! Check sheets names. ", sheet));
			return null;
		}
		List<ElementType> types = new ArrayList<ElementType>();
		for (int i = HEADER_ROWS; i < typesSheet.getRows(); i++) {
			String name = typesSheet.getCell(T_NAME_COL, i).getContents()
					.trim();
			String parentName = typesSheet.getCell(T_PARENT_COL, i)
					.getContents().trim();
			String isTransparent = typesSheet.getCell(T_TRANSPARENT_COL, i)
					.getContents().trim();
			String outConnNr = typesSheet.getCell(T_OUT_CON_COL, i)
					.getContents();
			if (name.equals("") && parentName.equals("")
					&& isTransparent.equals("") && outConnNr.equals(""))
				continue;
			String indexingStartFrom = typesSheet.getCell(
					T_INDEXING_START_FROM_COL, i).getContents().trim();
			String cableFlag = typesSheet.getCell(
					T_CABLE_FLAG_COL, i).getContents().trim();
			ElementType type = new ElementType();
			if (name.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Type name cannot be empty!", sheet, String.valueOf(
								convertIntToChar(T_NAME_COL)).toUpperCase(), i));
			type.setTypeName(name);
			if (typeMap.containsKey(name)) {
				handleFormatError(new InvalidMetadataFormatException(
						"Duplicate definition of type: " + name, sheet, String
								.valueOf(convertIntToChar(T_NAME_COL))
								.toUpperCase(), i));
				continue;
			}
			typeMap.put(name, type);
			parents.put(type, parentName);
			if (!isTransparent.equals("")
					&& !isTransparent.equals(TRANSPARENT_TRUE))
				handleFormatError(new InvalidMetadataFormatException(
						"Transparent flag should be " + TRANSPARENT_TRUE
								+ " or empty", sheet, String.valueOf(
								convertIntToChar(T_TRANSPARENT_COL))
								.toUpperCase(), i));
			type.setTransparent(isTransparent.equals(TRANSPARENT_TRUE));
			
			if (!cableFlag.equals("0")
					&& !cableFlag.equals("1") && !cableFlag.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Cable flag should be empty, " + 0
								+ " or " + 1, sheet, String.valueOf(
								convertIntToChar(T_CABLE_FLAG_COL))
								.toUpperCase(), i));
			type.setCable(cableFlag.equals("1"));

			String inConnNr = typesSheet.getCell(T_IN_CON_COL, i).getContents();
			if (!inConnNr.equals(""))
				try {
					type.setInputConnectorsNr(Integer.parseInt(inConnNr));
				} catch (NumberFormatException e) {
					handleFormatError(new InvalidMetadataFormatException(
							"Number of input connectors should be an integer number!",
							sheet, String.valueOf(
									convertIntToChar(T_IN_CON_COL))
									.toUpperCase(), i));
				}
			if (!outConnNr.equals(""))
				type.setOutputConnectorsNr(Integer.parseInt(outConnNr));

			Set<Field> fields = new HashSet<Field>();
			for (int j = T_FIELDS_COL; j < typesSheet.getColumns(); j++) {
				String fieldName = typesSheet.getCell(j, i).getContents();
				if (fieldName.equals(""))
					break;
				Field field = fieldMap.get(fieldName);
				if (field == null)
					handleFormatError(new InvalidMetadataFormatException(
							"Field named " + fieldName + " not found.", sheet,
							String.valueOf(convertIntToChar(j)).toUpperCase(),
							i));
				fields.add(field);
			}
			type.setFields(fields);
			types.add(type);
			int indexingStartValue = DEFAULT_FIRST_INDEX;
			if (!indexingStartFrom.equals("")) {
				try {
					indexingStartValue = Integer.parseInt(indexingStartFrom);
				} catch (NumberFormatException e) {
					handleFormatError(new InvalidMetadataFormatException(
							"Indexing start value should"
									+ " be an integer.\nWas: "
									+ indexingStartFrom,
							sheet,
							String
									.valueOf(
											convertIntToChar(T_INDEXING_START_FROM_COL))
									.toUpperCase(), i));
				}
			}
			startIndexingMap.put(type, indexingStartValue);

		}
		for (ElementType elementType : types) {
			String parentName = parents.get(elementType);
			elementType.setParentType(typeMap.get(parentName));
		}
		// connectInternally();
		return types;
	}

	private List<VirtualElement> getElementsStructure(Sheet structureSheet,
			boolean cable) throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(structureSheet.getName());
		if (structureSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found! Check sheets names. ", sheet));
			return null;
		}
		Map<VirtualElement, String> parents = new HashMap<VirtualElement, String>();
		Map<VirtualElement, Integer> rows = new HashMap<VirtualElement, Integer>();
		List<VirtualElement> elements = new ArrayList<VirtualElement>();
		for (int i = HEADER_ROWS; i < structureSheet.getRows(); i++) {
			String name = structureSheet.getCell(S_NAME_COL, i).getContents()
					.trim();
			String typeName = structureSheet.getCell(S_TYPE_COL, i)
					.getContents().trim();
			String parentName = structureSheet.getCell(S_PARENT_COL, i)
					.getContents().trim();
			if (name.equals("") && typeName.equals("") && parentName.equals(""))
				continue;
			if (name.equals("")) {
				handleFormatError(new InvalidMetadataFormatException(
						"Element identifier cannot be empty!", sheet, String
								.valueOf(convertIntToChar(S_NAME_COL))
								.toUpperCase(), i));
			}
			if (typeName.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Element type name cannot be empty!", sheet, String
								.valueOf(convertIntToChar(S_TYPE_COL))
								.toUpperCase(), i));
			ElementType et = typeMap.get(typeName);
			if (et == null) {
				handleFormatError(new InvalidMetadataFormatException(
						"Element type named " + typeName + " not found.",
						sheet, String.valueOf(convertIntToChar(S_TYPE_COL))
								.toUpperCase(), i));
				continue;
			}
			VirtualElement ve = cable ? new VirtualElement(et) : new VirtualElement(et);
			ve.setName(name);
			parents.put(ve, parentName);
			// rows.add(i - HEADER_ROWS, ve);
			rows.put(ve, Integer.valueOf(i));
			if (elementMap.containsKey(name)) {
				handleFormatError(new InvalidMetadataFormatException(
						"Duplicate definition of element: " + name, sheet,
						String.valueOf(convertIntToChar(E_IDENTIFIER_COL))
								.toUpperCase(), i));
				continue;
			}

			// if (cable)
			// cableMap.put(name, ve);
			// else
			elementMap.put(name, ve);

			elements.add(ve);
			int location = LOCATION_NOT_AVAILABLE;
			String locationStr = structureSheet.getCell(S_LOCATION_COL, i)
					.getContents().trim();

			try {
				if (!locationStr.equals(""))
					location = Integer.parseInt(locationStr);
			} catch (NumberFormatException e) {
				handleFormatError(new InvalidMetadataFormatException(
						"Position " + "should be an long integer value! Was: "
								+ locationStr, sheet,
						String.valueOf(convertIntToChar(S_LOCATION_COL))
								.toUpperCase(), i, e));
			}
			ve.setLocation(location);
		}
		for (VirtualElement virtualElement : elements) {
			String parentName = parents.get(virtualElement);
			int i = rows.get(virtualElement).intValue();
			VirtualElement parent = elementMap.get(parentName);
			if (!parentName.equals("") && parent == null)
				handleFormatError(new InvalidMetadataFormatException(
						"Element's parent, named " + parentName + " not found.",
						sheet, String.valueOf(convertIntToChar(S_PARENT_COL))
								.toUpperCase(), i));
			virtualElement.setParent(parent);
		}
		return elements;
	}

	public List<Connector> getConnections()
			throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(connectionsSheet.getName());
		if (connectionsSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found! Check sheets names. ", sheet));
			return null;
		}
		List<Connector> connectors = new ArrayList<Connector>();

		for (int i = HEADER_ROWS; i < connectionsSheet.getRows(); i++) {

			String outElementName = connectionsSheet.getCell(C_OUT_EL_COL, i)
					.getContents().trim();
			String inElementName = connectionsSheet.getCell(C_IN_EL_COL, i)
					.getContents().trim();

			String cableName = connectionsSheet.getCell(C_CABLE_COL, i)
					.getContents().trim();

			if (outElementName.equals("") && inElementName.equals("")
					&& cableName.equals(""))
				continue;
			if (
			// outElementName.equals("")
			// || inElementName.equals("")
			// ||
			cableName.equals("")) {
				handleFormatError(new InvalidMetadataFormatException(
						"Cable, output or input element identifier is missing!",
						sheet, "unspecified", i));
				continue;
			}

			VirtualElement outElt = outElementName.equals("") ? null
					: elementMap.get(outElementName);
			VirtualElement inElt = inElementName.equals("") ? null : elementMap
					.get(inElementName);
			VirtualElement cable = (VirtualElement) elementMap.get(cableName);

			// if(outElt == null){
			// handleFormatError(new InvalidMetadataFormatException("Element "
			// + outElementName + " undefined.", sheet, String.valueOf(
			// convertIntToChar(C_OUT_EL_COL)).toUpperCase(), i));
			// continue;
			// }
			// if(inElt == null){
			// handleFormatError(new InvalidMetadataFormatException("Element "
			// + inElementName + " undefined.", sheet, String.valueOf(
			// convertIntToChar(C_OUT_EL_COL)).toUpperCase(), i));
			// continue;
			// }
			if (cable == null) {
				handleFormatError(new InvalidMetadataFormatException("Cable "
						+ cableName + " undefined.", sheet, String.valueOf(
						convertIntToChar(C_OUT_EL_COL)).toUpperCase(), i));
				continue;
			}

			int firstIndexOut = 0;
			int firstIndexIn = 0;
			int outConnNr = firstIndexOut;
			int inConnNr = firstIndexIn;

			if (outElt != null) {
				firstIndexOut = startIndexingMap.containsKey(outElt
						.getElementType()) ? startIndexingMap.get(outElt
						.getElementType()) : DEFAULT_FIRST_INDEX;

				try {
					outConnNr = Integer.parseInt(connectionsSheet.getCell(
							C_OUT_CON_COL, i).getContents().trim());
				} catch (NumberFormatException e) {
					handleFormatError(new InvalidMetadataFormatException(
							"Output connector index "
									+ "should be an long integer value! Count starts with "
									+ firstIndexOut, sheet, String.valueOf(
									convertIntToChar(C_OUT_CON_COL))
									.toUpperCase(), i, e));
					continue;

				}
				
				if (outConnNr >= outElt.getElementType().getOutputConnectorsNr()
						+ firstIndexOut
						&& outConnNr < firstIndexOut) {
					handleFormatError(new InvalidMetadataFormatException(
							"Output connector index: "
									+ outConnNr
									+ "\n"
									+ "should be between "
									+ firstIndexOut
									+ " and type's: "
									+ outElt.getElementType()
									+ " Output Connectors Nr: "
									+ outElt.getElementType()
											.getOutputConnectorsNr()
									+ "\nCheck if both values are correct.", sheet,
							String.valueOf(convertIntToChar(C_OUT_CON_COL))
									.toUpperCase(), i));
					continue;
				}
			}

			if (inElt != null) {
				firstIndexIn = startIndexingMap.containsKey(inElt
						.getElementType()) ? startIndexingMap.get(inElt
						.getElementType()) : DEFAULT_FIRST_INDEX;
				try {
					inConnNr = Integer.parseInt(connectionsSheet.getCell(
							C_IN_CON_COL, i).getContents().trim());
				} catch (NumberFormatException e) {
					handleFormatError(new InvalidMetadataFormatException(
							"Input connector index "
									+ "should be an long integer value! Count starts with "
									+ firstIndexOut, sheet, String.valueOf(
									convertIntToChar(C_IN_CON_COL))
									.toUpperCase(), i, e));
					continue;

				}

				if (inConnNr >= inElt.getElementType().getInputConnectorsNr()
						+ firstIndexOut
						&& inConnNr < firstIndexIn) {
					handleFormatError(new InvalidMetadataFormatException(
							"Input connector index: "
									+ inConnNr
									+ "\n"
									+ "should be between "
									+ firstIndexIn
									+ " and type's: "
									+ inElt.getElementType()
									+ " Input Connectors Nr: "
									+ inElt.getElementType()
											.getInputConnectorsNr()
									+ "\nCheck if both values are correct.", sheet,
							String.valueOf(convertIntToChar(C_IN_CON_COL))
									.toUpperCase(), i));
					continue;
				}
			}

			Connector c = new Connector();
			c.setCable(cable);
			c.setElementIn(inElt);
			c.setElementOut(outElt);
			c.setPositionIn(inConnNr);
			c.setPositionOut(outConnNr);
			if(outElt != null)
				outElt.getOutputConnectors().add(c);
			if(inElt != null)
				inElt.getInputConnectors().add(c);
			connectors.add(c);
		}

		return connectors;
	}

	private List<PhysicalElement> getPhysicalElements(Sheet physicalSheet)
			throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(physicalSheet.getName());
		if (physicalSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found! Check sheets names. ", sheet));
			return null;
		}
		List<PhysicalElement> phElements = new ArrayList<PhysicalElement>();
		for (int i = HEADER_ROWS; i < physicalSheet.getRows(); i++) {
			String elementTypeName = physicalSheet.getCell(E_TYPE_COL, i)
					.getContents().trim();
			String label = physicalSheet.getCell(E_LABEL_COL, i).getContents()
					.trim();
			String virtualElementName = physicalSheet.getCell(E_IDENTIFIER_COL,
					i).getContents().trim();
			if (elementTypeName.equals("") && label.equals("")
					&& virtualElementName.equals(""))
				continue;
			if (elementTypeName.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Element type " + "cannot be null.", sheet, String
								.valueOf(convertIntToChar(E_TYPE_COL))
								.toUpperCase(), i));
			PhysicalElement pe = new PhysicalElement();
			ElementType et = typeMap.get(elementTypeName);
			if (et == null)
				handleFormatError(new InvalidMetadataFormatException(
						"Element type " + elementTypeName + " undefined.",
						sheet, String.valueOf(convertIntToChar(E_TYPE_COL))
								.toUpperCase(), i));
			pe.setElementType(et);
			if (label.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Element label " + "cannot be null.", sheet, String
								.valueOf(convertIntToChar(E_LABEL_COL))
								.toUpperCase(), i));
			pe.setLabel(label);
			if (virtualElementName.equals(""))
				handleFormatError(new InvalidMetadataFormatException(
						"Identifier " + "cannot be null.", sheet, String
								.valueOf(convertIntToChar(E_IDENTIFIER_COL))
								.toUpperCase(), i));
			VirtualElement ve = elementMap.get(virtualElementName);
			if (ve == null)
				handleFormatError(new InvalidMetadataFormatException(
						"Identifier " + virtualElementName + " not found.",
						sheet, String.valueOf(
								convertIntToChar(E_IDENTIFIER_COL))
								.toUpperCase(), i));
			// pe.setVirtualElementId(ve.getId());
			// ve.setPeId(peId);
			pe.setVirtualElement(ve);
			ve.setPhysicalElement(pe);
			complexMap.put(ve, pe);
			phElements.add(pe);
		}
		return phElements;
	}

	public List<ElementType> connectInternally() {
		List<ElementType> connected = new LinkedList<ElementType>();
		XLSSheets sheet = XLSSheets
				.findSheet(internalConnectionSheet.getName());
		if (internalConnectionSheet == null) {
			handleFormatError(new InvalidMetadataFormatException(
					"Sheet not found! Check sheets names. ", sheet));
			return connected;
		}

		// odczytaj typ elementu
		for (int i = HEADER_ROWS; i < internalConnectionSheet.getRows(); i++) {
			String elementName = internalConnectionSheet
					.getCell(IC_TYPE_COL, i).getContents().trim();
			if (elementName.equals(""))
				continue;
			ElementType et = typeMap.get(elementName);
			if (et == null) {
				handleFormatError(new InvalidMetadataFormatException(
						"Undefined type: " + elementName, sheet, String
								.valueOf(convertIntToChar(IC_TYPE_COL))
								.toUpperCase(), i));
				continue;
			}
			InternalConnection ic = new InternalConnection();
			int positionIn;
			try {
				positionIn = Integer.parseInt(internalConnectionSheet.getCell(
						IC_IN_COL, i).getContents().trim());
			} catch (NumberFormatException e) {
				handleFormatError(new InvalidMetadataFormatException(
						"Input connector index in Internal Connection should be an integer number!",
						sheet, String.valueOf(convertIntToChar(IC_IN_COL))
								.toUpperCase(), i));
				continue;
			}
			int positionOut;
			try {
				positionOut = Integer.parseInt(internalConnectionSheet.getCell(
						IC_OUT_COL, i).getContents().trim());
			} catch (NumberFormatException e) {
				handleFormatError(new InvalidMetadataFormatException(
						"Output connector index in Internal Connection should be an integer number!",
						sheet, String.valueOf(convertIntToChar(IC_OUT_COL))
								.toUpperCase(), i));
				continue;
			}
			ic.setPositionIn(positionIn);
			ic.setPositionOut(positionOut);
			ic.setTypeId(et.getId());
			et.getInternalConnections().add(ic);
			connected.add(et);
		}
		return connected;
	}

	@SuppressWarnings("unchecked")
	private static Connector getConnectorAt(int index, Iterator it) {
		Connector conn = null;
		for (int i = 0; i <= index; i++) {
			conn = (Connector) it.next();
		}
		return conn;
	}

	protected void handleFormatError(InvalidMetadataFormatException e) {
		throw e;
	}

	public List<VirtualElement> getElementsStructure() {
		List<VirtualElement> structure = getElementsStructure(modulesSheet,
				false);
		structure.addAll(getElementsStructure(cablesSheet, true));
		return structure;
	}

	public List<PhysicalElement> getPhysicalElements() {
		List<PhysicalElement> elements = getPhysicalElements(elementModulesSheet);
		elements.addAll(getPhysicalElements(elementCablesSheet));
		return elements;
	}

	public List<ComplexElement> getComplexElements() {
		List<ComplexElement> complexList = new ArrayList<ComplexElement>();
		for (VirtualElement ve : complexMap.keySet()) {
			ComplexElement ce = new ComplexElement();
			ce.setVeId(ve.getId());
			ce.setPeId(complexMap.get(ve).getId());
			complexList.add(ce);
		}
		return complexList;
	}

	protected enum XLSSheets {

		FIELDS(props.getProperty(FIELDS_SHEET)), TYPES(props
				.getProperty(TYPES_SHEET)), CONNECTIONS(props
				.getProperty(CONNECTIONS_SHEET)), STRUCTURE_MODULES(props
				.getProperty(STRUCTURE_MODULES_SHEET)), STRUCTURE_CABLES(props
				.getProperty(STRUCTURE_CABLES_SHEET)), INTERNAL(props
				.getProperty(INTERNAL_CONNECTIONS_SHEET)), ELEMENT_MODULES(
				props.getProperty(ELEMENT_MODULES_SHEET)), ELEMENT_CABLES(props
				.getProperty(ELEMENT_CABLES_SHEET)), CONNECTOR_NAMES(props
				.getProperty(CONNECTOR_NAMES_SHEET)), HISTORY(props
				.getProperty(HISTORY_SHEET));

		private String sheetName;

		XLSSheets(String sheetName) {
			this.sheetName = sheetName;
		}

		public String getName() {
			return sheetName;
		}

		public static XLSSheets findSheet(String name) {
			for (XLSSheets sheet : XLSSheets.values()) {
				if (sheet.getName().equals(name))
					return sheet;
			}
			System.out.println("No sheet with name: " + name);
			return null;
		}

	}

	static int convertCharToInt(char c) {
		return Character.getNumericValue(c) - 10;
	}

	static char convertIntToChar(int i) {
		return Character.forDigit(i + 10, 25);
	}

	public static void setTest(boolean test) {
		XLSMetadataReader.test = test;
	}
}
